﻿
CREATE PROCEDURE [dbo].[proc_Project_GetList_Interface_G]
	(
		@SearchWay INT,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@TitleNo INT,
		@IsShowMove INT,
		@UserName nvarchar(50),
		@IsFinish int,
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	DECLARE @CompanyId INT
	SELECT @CompanyId = CompanyId FROM Employee WHERE UserName = @UserName
	IF @CompanyId IS NULL
		SET @CompanyId = 0
	
	DECLARE @sql NVARCHAR(4000)
	Set @sql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	;WITH list As(Select Top 4500 ROW_NUMBER() OVER '

	IF @SearchWay = 0
		SET @sql = @sql + '(ORDER BY IsFinish,CreateDate Desc,Id Desc) '
	ELSE
		SET @sql = @sql + '(ORDER BY FinishDate Desc,Id Desc) '

	SET @sql = @sql + 'AS Row1,
		P.Id,
		P.ProjectId,
		P.OrderId,
		p.CusName,
		P.CostPrice,
		P.SalePrice,
		P.Description,
		P.ProductId,
		p.ProductName,
		p.TypeName,
		p.SupplierName,
		p.CompanyId,
		p.Salesman,
		p.SalesmanAccount,
		P.IsFinish,
		p.IsHedge As Row,
		NOE_Flag,ExecPerson,MoveCompanyId,'

	IF @SearchWay = 0
		BEGIN
			SET @sql = @sql + 'p.CreateDate,
			IsNull(p.FinishDate,''1910-01-01'') as FinishDate
			From Project p '
			SET @sql = @sql + 'Where CreateDate>=@sRq And CreateDate<=@eRq 
			And (' + Convert(varchar(10),@IsFinish) + '=2 Or IsFinish=' + Convert(varchar(10),@IsFinish) + ') '
		END
	ELSE
		BEGIN
			SET @sql = @sql + 'IsNull(p.CreateDate,''1910-01-01'') AS FinishDate,
			IsNull(p.FinishDate,''1910-01-01'') as CreateDate 
			From Project p '
			SET @sql = @sql + 'Where FinishDate>=@sRq And FinishDate<=@eRq 
			And (' + Convert(varchar(10),@TitleNo) + '=0 Or ProductId=' + Convert(varchar(10),@TitleNo) + ') AND isHedge = 0 '
		END

	IF @IsShowMove = 1
		SET @sql = @sql + 'And (ExecPerson=''' + @UserName + ''' Or MoveUserId=''' + @UserName + ''') '
	ELSE
		SET @sql = @sql + 'And ExecPerson=''' + @UserName + ''' AND CompanyId=' + CONVERT(VARCHAR(10),@CompanyId) + ' AND (MoveUserId IS NULL Or CompanyId=MoveCompanyId) '

	If @Stext <> ''
		SET @sql = @sql + 'And (CusName like''%' + @Stext + '%'' Or Salesman like''%' + @Stext + '%'')'

	SET @sql = @sql + ') Select *,
	(Select Count(0) From Project_Task Where ProjectId=list.ProjectId) As ChildCount,
	(Select Count(0) From list) As RecordCount,
	(select flag from [Product] where list.ProductId=Product.id) as flag From list
	Where Row1 Between ' + CONVERT(VARCHAR(10),@StartIndex) + ' And ' + CONVERT(VARCHAR(10),@EndIndex) + ' Order By Row1'
	
	-- Select @sql
	EXEC(@sql)
	
End